﻿CREATE TABLE [dbo].[ResUmaSetup] (
    [PrimaryKey]            SMALLINT        NOT NULL,
    [CodUnitate]            SMALLINT        CONSTRAINT [DF_ResUmaSetup_CodUnitate] DEFAULT ((0)) NOT NULL,
    [AnulCurent]            SMALLINT        CONSTRAINT [DF_ResUmaSetup_AnulCurent] DEFAULT ((0)) NOT NULL,
    [LunaCurenta]           SMALLINT        CONSTRAINT [DF_ResUmaSetup_LunaCurenta] DEFAULT ((0)) NOT NULL,
    [PropagaModificari]     BIT             CONSTRAINT [DF_ResUmaSetup_PropagaModificari] DEFAULT ((1)) NOT NULL,
    [Contracte]             BIT             CONSTRAINT [DF_ResUmaSetup_Contracte] DEFAULT ((0)) NOT NULL,
    [ProcentCASAngajator]   DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentCASAngajator] DEFAULT ((0)) NOT NULL,
    [SalarMinim]            DECIMAL (18, 2) CONSTRAINT [DF_ResUmaSetup_SalarMinim] DEFAULT ((0)) NOT NULL,
    [SalarMediuBrut]        DECIMAL (18, 2) CONSTRAINT [DF_ResUmaSetup_SalarMediuBrut] DEFAULT ((0)) NOT NULL,
    [NrSalariiMaxCAS]       SMALLINT        CONSTRAINT [DF_ResUmaSetup_NrSalariiMaxCAS] DEFAULT ((0)) NOT NULL,
    [ProcentCASAngajat]     DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentCASAngajat] DEFAULT ((0)) NOT NULL,
    [PlafonareCAS]          BIT             CONSTRAINT [DF_ResUmaSetup_PlafonareCAS] DEFAULT ((0)) NOT NULL,
    [ProcentSomajAngajat]   DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentSomajAngajat] DEFAULT ((0)) NOT NULL,
    [ProcentSomajAngajator] DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentSomajAngajator] DEFAULT ((0)) NOT NULL,
    [ProcentASSAngajat]     DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentASSAngajat] DEFAULT ((0)) NOT NULL,
    [ProcentASSAngajator]   DECIMAL (5, 2)  CONSTRAINT [DF_ResUmaSetup_ProcentASSAngajator] DEFAULT ((0)) NOT NULL,
    [ScanRes]               INT             NULL,
    [OrizExtent]            INT             NULL,
    [VertExtent]            INT             NULL,
    [ColorFormat]           INT             NULL,
    [ScanDebug]             BIT             NULL,
    [VersiuneCOR]           SMALLINT        DEFAULT ((0)) NOT NULL,
    [SituatieMilitara]      BIT             DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_ResUmaSetup] PRIMARY KEY CLUSTERED ([PrimaryKey] ASC, [CodUnitate] ASC)
);


GO

CREATE TRIGGER [dbo].[UpdateVersiuneCOR]
   ON  [dbo].[ResUmaSetup]
   AFTER UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Corect smallint
	SET @Corect = 0
	SELECT @corect = (i.VersiuneCOR - r.VersiuneCOR) FROM deleted r JOIN inserted i ON r.[CodUnitate] = i.[CodUnitate]
	IF @Corect < 0 
	BEGIN
		ROLLBACK
		RAISERROR ('O versiune noua nu se poate inlocui cu una mai veche!',-1,0)
		RETURN
	END
    UPDATE [AngajatiOcupatii] SET IDOcupatie = ocup.IDOcupatie FROM Ocupatii ocup,inserted i, Ocupatii oldocup,deleted d 
    WHERE  ocup.versiune = i.versiuneCOR AND oldocup.versiune = d.versiuneCOR AND oldocup.IdOcupatie = [AngajatiOcupatii].IDOcupatie
    AND oldocup.CorOcupatie=ocup.CorOcupatie

    UPDATE [GrileOcupatii] SET IDOcupatie = ocup.IDOcupatie FROM Ocupatii ocup,inserted i, Ocupatii oldocup,deleted d 
    WHERE  ocup.versiune = i.versiuneCOR AND oldocup.versiune = d.versiuneCOR AND oldocup.IdOcupatie = [GrileOcupatii].IDOcupatie
    AND oldocup.CorOcupatie=ocup.CorOcupatie

    UPDATE [GrileOcupatiiDep] SET IDOcupatie = ocup.IDOcupatie FROM Ocupatii ocup,inserted i, Ocupatii oldocup,deleted d 
    WHERE  ocup.versiune = i.versiuneCOR AND oldocup.versiune = d.versiuneCOR AND oldocup.IdOcupatie = [GrileOcupatiiDep].IDOcupatie
    AND oldocup.CorOcupatie=ocup.CorOcupatie


END